SQL Server Transaction

A transaction is a transaction against a database. This is a series of SQL operations that are executed as a single unit, meaning that all of the operations in the transaction have been successfully completed and loaded into the database, or that none of them are executed if an error occurs or if the transaction clearly occurs ( rolled back) rolled back .

Here are the basic concepts of transactions in SQL Server.

ACID Components
Connections to SQL Server follow ACID capabilities, which ensure reliability and data integrity,

Atomicity- Communication is atomic, that is, it has one function at a time. All operations in the transaction succeed and are guaranteed, or if an operation fails, the entire transaction is rolled back to maintain data consistency
Consistency- Consistency ensures that the database remains in a consistent state before and after operations.
Isolation- Interactions operate in isolation from each other until completion (involvement). This prevents connections from interfering with each other during execution.
Durability- Once a job is executed, its changes are permanently stored in the database even if the system fails.

Transaction Control Statements

SQL Server provides more information to process transactions:

BEGIN TRANSACTION Begins a new transaction.
COMMIT TRANSACTION Commits the current transaction, making its changes permanent.
ROLLBACK TRANSACTION Rolls back the current transaction, undoing its changes.
SAVE TRANSACTION Set a savepoint in the transaction that you can go back to later.

Implicit vs Explicit Transactions

Implicit Transactions 

By default, SQL Server operates in self-commit mode, where each individual SQL statement commits after success. However, you can assemble multiple SQL statements into an explicit transaction using BEGIN TRANSACTION and COMMIT or ROLLBACK.

Explicit Transactions 

These are transactions that are explicitly defined by BEGIN TRANSACTION, and require a manual commit (COMMIT) or rollback (ROLLBACK) to complete the transaction

Concurrency Control

SQL Server uses a concurrency control mechanism to manage multiple transactions to access the same data at the same time. This ensures that connections are isolated from each other to prevent incompatible data. SQL Server supports various isolation levels (such as Read Uncommitted, Read Committed, Repeatable Read, Serializable) that control the isolation level and locking behavior for transactions

Transaction Logs

SQL Server maintains transaction logs to write all transaction-induced changes to the database. These logs are necessary to ensure longevity and recovery in the event of a system failure.

BEGIN TRANSACTION;
UPDATE Employees
SET Salary = Salary * 1.05
WHERE Department = 'Engineering';
INSERT INTO AuditLog (Action, TableName, Timestamp)
VALUES ('Salary Update', 'Employees', GETDATE());
COMMIT TRANSACTION;

In the example above

• BEGIN TRANSACTION Begins a new transaction.
• In the UPDATE case, the compensation for employees in the technical department changes.
• The INSERT statement writes the update action to the AuditLog table.
• COMMIT TRANSACTION Commits the changes made by the transaction to the database.
   

If any part of the transaction goes into error (e.g., due to a constraint violation), it can use a ROLLBACK TRANSACTION to undo all changes made by the transaction and restore the database to its previous state

Example-

Here is a simple example to transfer money from source account to destination account using SQL Transaction,

Suppose we have an SQL table BankAccounts

CREATE TABLE BankAccounts (
   AccountID INT PRIMARY KEY,
   AccountNumber VARCHAR(20) NOT NULL,
   Balance DECIMAL(18, 2) NOT NULL
);

Suppose we want to transfer a specified amount from one account to another

USE MyTestDB
GO
CREATE PROCEDURE Proc_EmpCursor
(
@FromAccountID INT = 1,   -- Assuming AccountID 1 is the source account
@ToAccountID INT = 2,     -- Assuming AccountID 2 is the destination account
@TransferAmount DECIMAL(18, 2) = 100.00  -- Amount to transfer
)AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION;
-- Check if the source account has enough balance
DECLARE @FromBalance DECIMAL(18, 2);
SELECT @FromBalance = Balance FROM BankAccounts WHERE AccountID = @FromAccountID;
IF @FromBalance >= @TransferAmount
BEGIN
 -- Deduct the transfer amount from the source account
 UPDATE BankAccounts
 SET Balance = Balance - @TransferAmount
 WHERE AccountID = @FromAccountID;
 -- Add the transfer amount to the destination account
 UPDATE BankAccounts
 SET Balance = Balance + @TransferAmount
 WHERE AccountID = @ToAccountID;
 -- Log the transaction in an AuditLog table
 INSERT INTO AuditLog (Action, Description, Timestamp)
 VALUES ('Transfer', 'Transfer from AccountID ' + CAST(@FromAccountID AS VARCHAR) 
        + ' to AccountID ' + CAST(@ToAccountID AS VARCHAR) 
        + ' Amount: ' + CAST(@TransferAmount AS VARCHAR), GETDATE());
 -- Commit the transaction
 COMMIT TRANSACTION;
   
 PRINT 'Transfer successful.';
END
ELSE
BEGIN
 -- Rollback the transaction if there's insufficient balance
 ROLLBACK TRANSACTION;
 PRINT 'Insufficient balance in the source account. Transfer aborted.';
END

SET NOCOUNT OFF;
END

Also, Read: Explain the SQL CURSOR with example.